Model.query

Takeaways

• db.Model.query offers us the Query object. The Query object lets us generate SELECT statements that let us query and return slices of data from our database.
• Query has method chaining. You can chain one query method to another (indefinitely), getting back more query objects, until you chain it with a terminal method that returns a non-query object like count() , all() , first() , delete() , etc.
• The Query object can be accessed on a model using either:
  • MyModel.query directly on the model, or
  • db.session.query(MyModel) using db.session.query instead.

Query Methods

Here are some useful query methods to get to know.

Select records

all()

MyModel.query.all()

same as doing a SELECT * , fetching all records from the model's table. Returns a list of objects.

first()

MyModel.query.first()

Fetches just the first result. Returns either None or an object if found.

Filtering

filter_by

MyModel.query.filter_by(my_table_attribute='some value')

Similar to doing a SELECT * from ... WHERE SQL statement for filtering data by named attributes.

filter

Examples:

MyModel.query.filter(MyOtherModel.some_attr='some value')
OrderItem.query.filter(Product.id=3)

Similar to filter_by , but instead, you specify attributes on a given Model. It is more flexible than using filter_by itself, and is especially useful when querying from a joined table where you want to filter by attributes that span across multiple models.

Wait.. there's more to filtering!

You can filter on equality, inequality, like filtering ("fuzzy" string matching), IN, NOT IN, NULL, NOT NULL, etc.
Be sure to check out the SQLAlchemy docs reference on common filter operators here .

Ordering

order_by

MyModel.order_by(MyModel.created_at)
MyModel.order_by(db.desc(MyModel.created_at))

To order the results by a given attribute. Use db.desc to order in descending order.

limit

Order.query.limit(100).all()

limit(max_num_rows) limits the number of returned records from the query. ala LIMIT in SQL.

Aggregates

count()

Example:

query = Task.query.filter(completed=True)
query.count()

Returns an integer set to the number of records that would have been returned by running the query.

get( )

Get object by ID

model_id = 3
MyModel.query.get(model_id)

Returns the object as a result of querying the model by its primary key.

Bulk Deletes

Example:

query = Task.query.filter_by(category='Archived')
query.delete()

delete() does a bulk delete operation that deletes every record matching the given query.

Joined Queries

Example:

Driver.query.join('vehicles')

Query has a method join(<table_name>) for joining one model to another table.

Get this cheat sheet!

Click here to access a cheat sheet of handy SQLAlchemy Query methods to use.

Handy resources

• Docs for the SQLAlchemy Query API

QUIZ QUESTION: :

Here are some of the most common common Model.query methods. Can you match each of them with the corresponding SQL statement?

ANSWER CHOICES:

SELECT * from persons WHERE name = 'Amy';

SELECT * from persons;

SELECT COUNT(*) FROM persons;

SELECT * from persons WHERE id = 1;

SELECT * from persons LIMIT 1;

SELECT * from persons LIMIT 1;

SELECT COUNT(*) FROM persons;

SELECT * from persons;

SELECT * from persons WHERE name = 'Amy';

SELECT * from persons WHERE id = 1;

Practice

Either on your own machine or in the interactive workspace below, practice using the Query object and gain familiarity with it.

First, create a model and insert some records into the database table for it (e.g. using psql). Then, experiment with querying the data.

Let's say you created a Users table with a name attribute. Example exercises you can do include:

• Implement a query to filter all users by name 'Bob'.
• Implement a LIKE query to filter the users for records with a name that includes the letter "b".
• Return only the first 5 records of the query above.
• Re-implement the LIKE query using case-insensitive search.
• Return the number of records of users with name 'Bob'.

Example code

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://my_user@localhost:5432/example'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class User(db.Model):
  __tablename__ = 'users'
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(), nullable=False)

  def __repr__(self):
      return f'<User {self.id}, {self.name}>'


db.create_all()

Interactive Workspace

Below is an interactive workspace you can use to try out Query commands.